package com.slipper.core.database.support;

import com.slipper.core.database.IResolver;
import com.slipper.core.database.vo.FieldVO;
import com.slipper.core.database.vo.IndexFieldVO;
import com.slipper.core.database.vo.IndexVO;
import com.slipper.core.database.vo.TableVO;
import com.slipper.exception.CommonException;
import com.slipper.util.Tool;

import java.util.*;
import java.util.stream.Collectors;

public class SqlserverResolver implements IResolver {
    private final static Map<String, String> dataTypeMap = new HashMap<>() {{
        put("smallint", "整数");
        put("integer", "整数");
        put("int", "整数");
        put("bigint", "长整数");
        put("real", "小数");
        put("double", "小数");
        put("numeric", "小数");
        put("money", "小数");
        put("date", "日期型");
        put("datetime", "日期时间");
        put("datetime2", "日期时间");
        put("smalldatetime", "日期时间");
        put("datetimeoffset", "日期时间");
        put("varbinary", "定长字符");
        put("varchar", "字符");
        put("nvarchar", "字符");
        put("bit", "布尔");
        put("text", "大文本");
        put("ntext", "大文本");
        put("binary", "二进制");
    }};

    private final static Map<String, String> fieldTypeMap = new HashMap<>() {{
        put("字符", "nvarchar");
        put("整数", "int");
        put("小数", "numeric");
        put("日期", "varchar");
        put("时间", "bigint");
        put("图片", "varchar");
        put("视频", "varchar");
        put("附件", "varchar");
        put("联动", "varchar");
        put("布尔", "bit");
        put("大文本", "nvarchar(max)");
        put("长整数", "bigint");
        put("二进制", "binary");
        put("日期型", "date");
        put("日期时间", "datetime");
        put("定长字符", "nvarchar");
    }};

    @Override
    public String tableTotalSQL(String tableName) {
        return "select count(*) " +
                "from information_schema.tables " +
                "where table_type = 'BASE TABLE' " +
                "   and table_catalog = db_name() " +
                "   and table_schema = schema_name() " +
                "   and lower(table_name) = '" + tableName + "'";
    }

    @Override
    public String tableListSQL(String tableName) {
        return """
                select\s
                    lower(t.name) as "table_name",\s
                    cast(ep.value as nvarchar(500)) as "table_comment"\s
                from sys.tables as t\s
                left join sys.extended_properties as ep on ep.major_id = t.object_id\s
                	and ep.minor_id = 0\s
                	and ep.class = 1\s
                	and ep.name = N'MS_Description'\s
                where schema_name(t.schema_id) = schema_name()\s
                %s\s
                order by t.name
                """.formatted(Tool.isNotBlank(tableName)
                    ? "and (lower(t.name) like '%" + tableName + "%' " +
                        "or cast(ep.value as nvarchar(500)) like '%" + tableName + "%')"
                    : "");
    }

    @Override
    public String tableColumnListSQL(String ...tableNames) {
        List<String> filterList = new ArrayList<>();
        for (String tableName : tableNames) {
            filterList.add(" lower(c.table_name) like '" + tableName + "' ");
        }
        return "select " +
                "    lower(c.table_name) \"table_name\", " +
                "    lower(c.column_name) \"column_name\", " +
                "    lower(c.data_type) \"column_type\", " +
                "    case when kcu.constraint_type is not null then '1' else '0' end \"column_pk\", " +
                "    case when c.is_nullable='YES' then '1' else '0' end \"column_null\", " +
                "    c.character_maximum_length \"column_length\", " +
                "    c.numeric_precision \"column_precision\", " +
                "    c.numeric_scale \"column_scale\", " +
                "    c.ordinal_position \"column_ordinal\" " +
                "from information_schema.columns c " +
                "    left join (" +
                "        select " +
                "            kcu.table_catalog, " +
                "            tc.constraint_type, " +
                "            kcu.table_schema, " +
                "            kcu.table_name, " +
                "            kcu.column_name  " +
                "        from information_schema.key_column_usage kcu, information_schema.table_constraints tc " +
                "        where kcu.table_catalog=tc.table_catalog " +
                "            and kcu.table_schema=tc.table_schema " +
                "            and kcu.table_name=tc.table_name " +
                "            and kcu.constraint_name = tc.constraint_name " +
                "            and tc.constraint_type = 'PRIMARY KEY') kcu on c.table_catalog=kcu.table_catalog " +
                "        and c.table_schema=kcu.table_schema " +
                "        and c.table_name=kcu.table_name " +
                "        and c.column_name=kcu.column_name " +
                "where c.table_catalog = db_name() and c.table_schema = schema_name() " +
                "    and (" + String.join(" or ", filterList) + ") " +
                "order by c.table_name, c.ordinal_position ";
    }

    @Override
    public String tableIndexSQL(String ...tableNames) {
        List<String> filterList = new ArrayList<>();
        for (String tableName : tableNames) {
            filterList.add(" lower(t.name) like '" + tableName + "' ");
        }
        return "select " +
                "    t.name as \"table_name\", " +
                "    i.name as \"index_name\", " +
                "    case when i.is_unique='true' then '1' else '0' end \"unique\", " +
                "    c.name as \"column_name\", " +
                "    ic.key_ordinal as \"ordinal\", " +
                "    case when ic.is_descending_key = 1 then '降序' else '升序' end \"orderType\" " +
                "from sys.indexes i " +
                "    left join sys.tables t on i.object_id = t.object_id " +
                "    left join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id " +
                "    left join sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id " +
                "    left join sys.schemas s on t.schema_id = s.schema_id " +
                "where s.name = schema_name() " +
                "    and i.is_primary_key = 0 " +
                "    and i.name is not null " +
                "    and c.name is not null " +
                "    and (" + String.join(" or ", filterList) + ") " +
                "order by t.name, i.name, ic.key_ordinal ";
    }

    @Override
    public String columnSQL(String tableName) {
        return """
                select
                    lower(c.name) as "name",
                    lower(t.name) as "type",
                    c.max_length as "length",
                    c.precision "precision",
                    c.scale "scale",
                    cast(ep.value as nvarchar(500)) as "comment",
                    isnull(pk.is_pk, 0) as "pk"
                from sys.columns c
                join sys.types t on t.user_type_id = c.user_type_id
                join sys.tables tb on tb.object_id = c.object_id
                left join sys.extended_properties ep
                    on ep.major_id = c.object_id
                    and ep.minor_id = c.column_id
                    and ep.class = 1
                    and ep.name = 'MS_Description'
                left join (
                    select ic.object_id, ic.column_id, 1 as is_pk
                    from sys.index_columns ic
                    join sys.indexes i
                        on i.object_id = ic.object_id
                        and i.index_id = ic.index_id
                    where i.is_primary_key = 1
                ) pk on pk.object_id = c.object_id and pk.column_id = c.column_id
                where schema_name(tb.schema_id) = schema_name() and lower(tb.name) = '%s'
                order by c.column_id;
                """.formatted(tableName);
    }

    @Override
    public String[] createTableDDL(TableVO table) {
        List<String> fieldList = new ArrayList<>();
        List<String> pkFieldList = new ArrayList<>();

        table.getFieldList().sort(Comparator.comparingInt(FieldVO::getOrdinal));
        for (FieldVO field : table.getFieldList()) {
            String fieldType = fieldTypeMap.get(field.getType());
            if (Tool.isBlank(fieldType)) throw new CommonException("不支持此数据类型【" + field.getType() + "】！");

            String fieldSql = wrapSymbol(field.getName()) + " " + fieldType;
            fieldSql += switch (field.getType()) {
                case "小数" -> "(" + field.getLength() + ", " + field.getPrecision() + ")";
                case "字符", "字符串", "日期" -> "(" + (field.getLength() == null ? 20 : field.getLength()) + ")";
                case "图片", "视频", "附件", "联动" -> "(20)";
                default -> "";
            };
            fieldSql += field.isNullable() ? "" : " not null ";
            fieldList.add(fieldSql);

            if (field.isPrimaryKey()) {
                pkFieldList.add(field.getName());
            }
        }

        String ddl = "create table " + wrapSymbol(table.getName()) + "(";
        ddl += String.join(", ", fieldList);
        if (!pkFieldList.isEmpty()) {
            ddl += ", primary key(";
            ddl += pkFieldList.stream().map(this::wrapSymbol).collect(Collectors.joining(", "));
            ddl += ")";
        }
        ddl += ")";

        List<String> resultList = new LinkedList<>();
        resultList.add(ddl);

        for (IndexVO index : table.getIndexList()) {
            String indexSql = "create ";
            if (index.isUnique()) {
                indexSql += " unique ";
            }
            indexSql += " index " + wrapSymbol(index.getName()) + " on " + wrapSymbol(table.getName());
            index.getFieldList().sort(Comparator.comparingInt(IndexFieldVO::getOrdinal));
            indexSql += "(";
            indexSql += index.getFieldList().stream()
                    .map(item -> wrapSymbol(item.getName()) + " " + (item.isAscending() ? "asc" : "desc"))
                    .collect(Collectors.joining(", "));
            indexSql += ")";
            resultList.add(indexSql);
        }
        return resultList.toArray(new String[0]);
    }

    @Override
    public String[] createOrUpdateViewDDL(String viewName, String sql) {
        return new String[] {
                "if exists (select * from sys.views where name = '" + viewName + "') drop view " + wrapSymbol(viewName),
                "create view " + wrapSymbol(viewName) + " as " + sql
        };
    }

    @Override
    public String dropTableDDL(String tableName) {
        return "drop table " + wrapSymbol(tableName);
    }

    @Override
    public String dropViewDDL(String viewName) {
        return "drop view " + wrapSymbol(viewName);
    }

    @Override
    public String addFieldDDL(String tableName, FieldVO field) {
        String fieldType = fieldTypeMap.get(field.getType());
        if (Tool.isBlank(fieldType)) throw new CommonException("不支持此数据类型【" + field.getType() + "】！");

        String fieldSql = "alter table " + wrapSymbol(tableName) + " add ";
        fieldSql += wrapSymbol(field.getName()) + " " + fieldType;
        fieldSql += switch (field.getType()) {
            case "小数" -> "(" + field.getLength() + ", " + field.getPrecision() + ")";
            case "字符", "字符串", "日期" -> "(" + (field.getLength() == null ? 20 : field.getLength()) + ")";
            case "图片", "视频", "附件", "联动" -> "(20)";
            default -> "";
        };
        fieldSql += field.isNullable() ? "" : " not null ";
        return fieldSql;
    }

    @Override
    public String[] modifyFieldDDL(String tableName, FieldVO newField, FieldVO oldField) {
        String newFieldType = fieldTypeMap.get(newField.getType());
        String oldFieldType = fieldTypeMap.get(oldField.getType() == null ? "unknown" : oldField.getType());
        if (Tool.isBlank(newFieldType)) throw new CommonException("不支持此数据类型【" + newField.getType() + "】！");

        List<String> ddlList = new LinkedList<>();
        if (!oldField.getName().equals(newField.getName())) {
            ddlList.add("alter table " + wrapSymbol(tableName)
                    + " rename column " + wrapSymbol(oldField.getName()) + " to "
                    + wrapSymbol(newField.getName()));
        }

        if (!newFieldType.equals(oldFieldType) || !Objects.equals(newField.getLength(), oldField.getLength())) {
            String fieldSql = "alter table " + wrapSymbol(tableName) + " alter column ";
            fieldSql += wrapSymbol(newField.getName()) + " " + newFieldType;
            fieldSql += switch (newField.getType()) {
                case "小数" -> "(" + newField.getLength() + ", " + newField.getPrecision() + ")";
                case "字符", "字符串", "日期" -> "(" + (newField.getLength() == null ? 20 : newField.getLength()) + ")";
                case "图片", "视频", "附件", "联动" -> "(20)";
                default -> "";
            };
            fieldSql += newField.isNullable() ? " null " : " not null ";
            ddlList.add(fieldSql);
        }

        return ddlList.toArray(new String[0]);
    }

    @Override
    public String addIndexDDL(String tableName, IndexVO index) {
        return "create " + (index.isUnique() ? "unique" : "") + " index "
                + wrapSymbol(index.getName()) + " on "
                + wrapSymbol(tableName)
                + "("
                + index.getFieldList().stream()
                .map(item -> wrapSymbol(item.getName()) + " " + (item.isAscending() ? "asc" : "desc"))
                .collect(Collectors.joining(", "))
                + ")";
    }

    @Override
    public String dropIndexDDL(String tableName, String indexName) {
        return "drop index " + wrapSymbol(indexName) + " on " + tableName;
    }

    @Override
    public void parseTable(Map<String, TableVO> tableMap, List<Map<String, Object>> fieldList) {
        fieldList.forEach(data -> {
            String tableName = (String) getValue("table_name", data);
            TableVO table = tableMap.get(tableName);
            if (table == null) {
                table = new TableVO();
                table.setName(tableName);
                tableMap.put(tableName, table);
            }

            FieldVO field = new FieldVO();
            field.setName((String) getValue("column_name", data));


            Object columnLength = getValue("column_length", data);
            Object columnPrecision = getValue("column_precision", data);
            Object columnScale = getValue("column_scale", data);

            Long length = null;
            Integer precision = null;
            if (columnLength != null) {
                length = Long.parseLong(columnLength.toString());
            } else if (columnPrecision != null) {
                length = Long.parseLong(columnPrecision.toString());
                if (columnScale != null) {
                    precision = Integer.parseInt(columnScale.toString());
                }
            }
            field.setType(changeType((String) getValue("column_type", data), length));
            field.setLength(length);
            field.setPrecision(precision);
            field.setPrimaryKey((String) getValue("column_pk", data));
            field.setNullable((String) getValue("column_null", data));

            Object ordinal = getValue("column_ordinal", data);
            field.setOrdinal(ordinal == null ? 99 : Integer.parseInt(ordinal.toString()));
            table.addField(field);
        });
    }

    @Override
    public void parseIndex(Map<String, TableVO> tableMap, List<Map<String, Object>> indexList) {
        indexList.forEach(data -> {
            String tableName = (String) getValue("table_name", data);
            if (Tool.isBlank(tableName)) return;

            TableVO table = tableMap.get(tableName);
            String indexName = (String) getValue("index_name", data);
            if (Tool.isBlank(indexName)) return;

            IndexVO index = null;
            for (IndexVO temp : table.getIndexList()) {
                if (temp.getName().equalsIgnoreCase(indexName)) {
                    index = temp;
                    break;
                }
            }
            if (index == null) {
                index = new IndexVO();
                index.setName(indexName);
                index.setUnique((String) getValue("unique", data));
                table.addIndex(index);
            }

            IndexFieldVO field = new IndexFieldVO();
            field.setName(unwrapSymbol((String) getValue("column_name", data)));
            field.setOrderType((String) getValue("orderType", data));

            Object ordinal = getValue("ordinal", data);
            field.setOrdinal(ordinal == null ? 99 : Integer.parseInt(ordinal.toString()));
            index.addField(field);
        });
    }

    public String changeType(String fieldType, Long fieldLength) {
        if (Tool.isBlank(fieldType)) throw new CommonException("未知数据库类型【" + fieldType + "】！");
        String dataType = dataTypeMap.get(fieldType.toLowerCase());
        if (Tool.isBlank(dataType)) throw new CommonException("未知数据库类型【" + fieldType + "】！");
        return fieldType.equalsIgnoreCase("nvarchar")
                && fieldLength != null
                && (fieldLength < 0 || fieldLength > 4000)
                ? "大文本" : "字符";
    }

    public boolean isEqualsType(String dataType, String columnType) {
        return fieldTypeMap.get(dataType) != null && fieldTypeMap.get(dataType).equalsIgnoreCase(columnType);
    }

    @Override
    public Map<String, String> getDataTypeMap() {
        return dataTypeMap;
    }

    public Object getValue(String key, Map<String, Object> data) {
        for (Map.Entry<String, Object> entry : data.entrySet()) {
            if (entry.getKey().equalsIgnoreCase(key)) {
                return entry.getValue();
            }
        }
        return null;
    }

    public String wrapSymbol(String value) {
        if (value.trim().startsWith("\"")) return value;
        return "\"" + value.trim().toLowerCase() + "\"";
    }

    public String unwrapSymbol(String value) {
        if (Tool.isBlank(value)) return value;

        if (value.trim().startsWith("\"")) {
            value = value.trim().substring(1);
        }
        if (value.trim().endsWith("\"")) {
            value = value.trim().substring(0, value.length() - 1);
        }
        return value;
    }
}
